﻿CREATE PROCEDURE [dbo].[sp_Get_Agent]
	@MdNumber Varchar(4),
	@AgentId int,
	@AgentName varchar(100),
	@IsOrder bit
AS
	IF @AgentId IS NULL and @AgentName is null
	BEGIN
		IF @IsOrder IS NULL
		BEGIN
			SELECT [ID], [Name], [MdNumber], [TERRITORY], [AgentCode], [Latitude], [Longitude]
			FROM [dbo].[Agents] WHERE [MdNumber] = @MdNumber
		END
		ELSE IF @IsOrder = 0
		BEGIN
			SELECT [ID], [Name], [MdNumber], [TERRITORY], [AgentCode], [Latitude], [Longitude]
			FROM [dbo].[Agents] 
			WHERE [MdNumber] = @MdNumber
			AND Name NOT IN (SELECT DISTINCT SUBSTRING(AgentName, CHARINDEX(' ', AgentName) + 1, LEN(AgentName)) 
			FROM ZipInfo WHERE MdNumber = @MdNumber AND AgentName IS NOT NULL)
		END
	END
	ELSE IF @MdNumber IS NULL AND @AgentId IS NULL AND @AgentName IS NOT NULL
	BEGIN
		SELECT [ID], [Name], [MdNumber], [TERRITORY], [AgentCode], [Latitude], [Longitude]
			FROM [dbo].[Agents] WHERE [Name] = @AgentName
	END
	ELSE IF @AgentId IS NOT NULL AND @AgentName is null
	BEGIN
		SELECT [ID]
      ,[Name]
      ,[Latitude]
      ,[Longitude]
      ,[MdNumber]
      ,[ActiveContacts]
      ,[ActiveAppointMade]
      ,[ActiveAppointKept]
      ,[ActivePresentation]
      ,[ActiveAppointSale]
      ,[LapsedContacts]
      ,[LapsedAppointMade]
      ,[LapsedAppointKept]
      ,[LapsedPresentation]
      ,[LapsedAppointSale]
      ,[NeighborContacts]
      ,[NeighborAppointMade]
      ,[NeighborAppointKept]
      ,[NeighborPresentation]
      ,[NeighborAppointSale]
      ,[BusinessContacts]
      ,[BusinessAppointMade]
      ,[BusinessAppointKept]
      ,[BusinessPresentation]
      ,[BusinessAppointSale]
      ,[RefContacts]
      ,[RefAppointMade]
      ,[RefAppointKept]
      ,[RefPresentation]
      ,[RefAppointSale]
      ,[TSystemContacts]
      ,[TSystemAppointMade]
      ,[TSystemAppointKept]
      ,[TSystemPresentation]
      ,[TSystemAppointSale]
      ,[TargetMarketContacts]
      ,[TargetMarketAppointMade]
      ,[TargetMarketAppointKept]
      ,[TargetMarketPresentation]
      ,[TargetMarketAppointSale]
      ,[FieldContacts]
      ,[FieldAppointMade]
      ,[FieldAppointKept]
      ,[FieldPresentation]
      ,[FieldAppointSale]
      ,[AvgAPVperApp]
	  ,az.ActiveLeadCount
	  ,az.LapsedLeadCount
	  ,az.NeighborLeadCount
	  ,az.BusinesLeadCount
		FROM [dbo].[Agents] a
		LEFT JOIN 
		(SELECT substring(AgentName, CHARINDEX(' ', AgentName) + 1, LEN(AgentName)) as assignment, 
		SUM(ActiveLeadCount) AS ActiveLeadCount, 
		SUM(LapsedLeadCount) AS LapsedLeadCount, 
		SUM(NeighborLeadCount) AS NeighborLeadCount, 
		SUM(BusinesLeadCount) AS BusinesLeadCount
		FROM dbo.ZipInfo WHERE AgentName IS NOT NULL GROUP BY substring(AgentName, CHARINDEX(' ', AgentName) + 1, LEN(AgentName))) az
		ON a.Name = az.assignment
		WHERE a.[MdNumber] = @MdNumber AND a.[ID] = @AgentId
	END
	ELSE
	BEGIN
	SELECT [ID], [Name], [MdNumber], [TERRITORY], [AgentCode], [Latitude], [Longitude]
		FROM [dbo].[Agents] WHERE [MdNumber] = @MdNumber AND [Name] = @AgentName
	END